using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace FrameWorkSystem.BusinessLayer.DataLayer
{
	/// <summary>
	/// Data access layer class for Sys_Users
	/// </summary>
	class Sys_UsersSql : DataLayerBase 
	{

        #region Constructor

		/// <summary>
		/// Class constructor
		/// </summary>
		public Sys_UsersSql()
		{
			// Nothing for now.
		}

        #endregion

        #region Public Methods

        /// <summary>
        /// insert new row in the table
        /// </summary>
		/// <param name="businessObject">business object</param>
		/// <returns>true of successfully insert</returns>
		public bool Insert(Sys_Users businessObject)
		{
			SqlCommand	sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "INSERT [Sys_Users]([User_Name],[Passwords],[Full_Name],[Group_ID],[Is_Mon],[Is_Tue],[Is_Wed],[Is_Thu],[Is_Fri],[Is_Sad],[Is_Sun],[Start_Date],[End_Date],[IsEdit],[IsDelete],[IsNew])VALUES(@User_Name,@Passwords,@Full_Name,@Group_ID,@Is_Mon,@Is_Tue,@Is_Wed,@Is_Thu,@Is_Fri,@Is_Sad,@Is_Sun,@Start_Date,@End_Date,@IsEdit,@IsDelete,@IsNew)";
			sqlCommand.CommandType = CommandType.Text;

			// Use connection object of base class
			sqlCommand.Connection = MainConnection;

			try
			{
                
				sqlCommand.Parameters.Add(new SqlParameter("@User_Name", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.User_Name));
				sqlCommand.Parameters.Add(new SqlParameter("@Passwords", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Passwords));
				sqlCommand.Parameters.Add(new SqlParameter("@Full_Name", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Full_Name));
				sqlCommand.Parameters.Add(new SqlParameter("@Group_ID", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Group_ID));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Mon", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Mon));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Tue", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Tue));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Wed", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Wed));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Thu", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Thu));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Fri", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Fri));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Sad", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Sad));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Sun", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Sun));
				sqlCommand.Parameters.Add(new SqlParameter("@Start_Date", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Start_Date));
				sqlCommand.Parameters.Add(new SqlParameter("@End_Date", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.End_Date));
				sqlCommand.Parameters.Add(new SqlParameter("@IsEdit", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IsEdit));
				sqlCommand.Parameters.Add(new SqlParameter("@IsDelete", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IsDelete));
				sqlCommand.Parameters.Add(new SqlParameter("@IsNew", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IsNew));

								
				MainConnection.Open();
				
				sqlCommand.ExecuteNonQuery();
                
				return true;
			}
			catch(Exception ex)
			{				
				throw new Exception("Sys_Users::Insert::Error occured.", ex);
			}
			finally
			{			
				MainConnection.Close();
				sqlCommand.Dispose();
			}
		}

         /// <summary>
        /// update row in the table
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <returns>true for successfully updated</returns>
        public bool Update(Sys_Users businessObject)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "UPDATE [Sys_Users] SET [User_Name] = @User_Name,[Passwords] = @Passwords,[Full_Name] = @Full_Name,[Group_ID] = @Group_ID,[Is_Mon] = @Is_Mon,[Is_Tue] = @Is_Tue,[Is_Wed] = @Is_Wed,[Is_Thu] = @Is_Thu,[Is_Fri] = @Is_Fri,[Is_Sad] = @Is_Sad,[Is_Sun] = @Is_Sun,[Start_Date] = @Start_Date,[End_Date] = @End_Date,[IsEdit] = @IsEdit,[IsDelete] = @IsDelete,[IsNew] = @IsNew WHERE [User_Name] = @User_Name";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
				sqlCommand.Parameters.Add(new SqlParameter("@User_Name", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.User_Name));
				sqlCommand.Parameters.Add(new SqlParameter("@Passwords", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Passwords));
				sqlCommand.Parameters.Add(new SqlParameter("@Full_Name", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Full_Name));
				sqlCommand.Parameters.Add(new SqlParameter("@Group_ID", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Group_ID));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Mon", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Mon));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Tue", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Tue));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Wed", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Wed));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Thu", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Thu));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Fri", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Fri));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Sad", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Sad));
				sqlCommand.Parameters.Add(new SqlParameter("@Is_Sun", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Is_Sun));
				sqlCommand.Parameters.Add(new SqlParameter("@Start_Date", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Start_Date));
				sqlCommand.Parameters.Add(new SqlParameter("@End_Date", SqlDbType.DateTime, 8, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.End_Date));
				sqlCommand.Parameters.Add(new SqlParameter("@IsEdit", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IsEdit));
				sqlCommand.Parameters.Add(new SqlParameter("@IsDelete", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IsDelete));
				sqlCommand.Parameters.Add(new SqlParameter("@IsNew", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.IsNew));

                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Sys_Users::Update::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }

        public Sys_Users CheckUser(string Uname, string Pwd, DateTime date)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM  Sys_Users WHERE [User_Name] = @User_Name COLLATE SQL_Latin1_General_CP1_CS_AS AND Passwords = @Password COLLATE SQL_Latin1_General_CP1_CS_AS	AND CONVERT (VARCHAR(10), @date, 112) BETWEEN CONVERT(VARCHAR(10),Start_Date, 112) AND  CONVERT(VARCHAR(10),End_Date, 112)";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@User_Name", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, Uname));
                sqlCommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, Pwd));
                sqlCommand.Parameters.Add(new SqlParameter("@date", SqlDbType.DateTime, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, date));

                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                if (dataReader.Read())
                {
                    Sys_Users businessObject = new Sys_Users();

                    PopulateBusinessObjectFromReader(businessObject, dataReader);                    
                    return businessObject;
                }                
            }
            catch //(Exception ex)
            {
                return null;//throw new Exception("Sys_Users::SelectByPrimaryKey::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
            return null;
        }

        public Sys_Users SelectByPrimaryKey(Sys_UsersKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM [dbo].[Sys_Users]	WHERE [User_Name] = @User_Name";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

				sqlCommand.Parameters.Add(new SqlParameter("@User_Name", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.User_Name));

                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                if (dataReader.Read())
                {
                    Sys_Users businessObject = new Sys_Users();

                    PopulateBusinessObjectFromReader(businessObject, dataReader);

                    return businessObject;
                }
                else
                {
                    return null;
                }
            }
            catch //(Exception ex)
            {
                return null;//throw new Exception("Sys_Users::SelectByPrimaryKey::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select all rescords
        /// </summary>
        /// <returns>list of Sys_Users</returns>
        public List<Sys_Users> SelectAll()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT * FROM [Sys_Users]";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {                
                throw new Exception("Sys_Users::SelectAll::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>list of Sys_Users</returns>
        public List<Sys_Users> SelectByField(string fieldName, object value)
        {

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SELECT [User_Name], [Passwords], [Full_Name], [Group_ID], [Is_Mon], [Is_Tue], [Is_Wed], [Is_Thu], [Is_Fri], [Is_Sad], [Is_Sun], [Start_Date], [End_Date], [IsEdit], [IsDelete], [IsNew] FROM [dbo].[Sys_Users] WHERE [' + @FieldName  + '] = ''' + @Value + '''";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@FieldName", fieldName));
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));

                MainConnection.Open();
                
                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {
                throw new Exception("Sys_Users::SelectByField::Error occured.", ex);
            }
            finally
            {

                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>true for successfully deleted</returns>
        public bool Delete(Sys_UsersKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "DELETE FROM [Sys_Users] WHERE [User_Name] = @User_Name";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

				sqlCommand.Parameters.Add(new SqlParameter("@User_Name", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.User_Name));


                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Sys_Users::DeleteByKey::Error occured.", ex);
            }
            finally
            {                
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>true for successfully deleted</returns>
        public bool DeleteByField(string fieldName, object value)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "DELETE FROM [dbo].[Sys_Users] WHERE [' + @FieldName  + '] = ''' + @Value + '''";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@FieldName", fieldName));
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));
                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;

            }
            catch (Exception ex)
            {                
                throw new Exception("Sys_Users::DeleteByField::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        #endregion

        #region Private Methods

        /// <summary>
        /// Populate business object from data reader
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <param name="dataReader">data reader</param>
        internal void PopulateBusinessObjectFromReader(Sys_Users businessObject, IDataReader dataReader)
        {


				businessObject.User_Name = dataReader.GetString(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.User_Name.ToString()));

				businessObject.Passwords = dataReader.GetString(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Passwords.ToString()));

				businessObject.Full_Name = dataReader.GetString(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Full_Name.ToString()));

				businessObject.Group_ID = dataReader.GetString(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Group_ID.ToString()));

				businessObject.Is_Mon = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Is_Mon.ToString()));

				businessObject.Is_Tue = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Is_Tue.ToString()));

				businessObject.Is_Wed = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Is_Wed.ToString()));

				businessObject.Is_Thu = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Is_Thu.ToString()));

				businessObject.Is_Fri = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Is_Fri.ToString()));

				businessObject.Is_Sad = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Is_Sad.ToString()));

				businessObject.Is_Sun = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Is_Sun.ToString()));

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Start_Date.ToString())))
				{
					businessObject.Start_Date = dataReader.GetDateTime(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.Start_Date.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.End_Date.ToString())))
				{
					businessObject.End_Date = dataReader.GetDateTime(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.End_Date.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.IsEdit.ToString())))
				{
					businessObject.IsEdit = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.IsEdit.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.IsDelete.ToString())))
				{
					businessObject.IsDelete = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.IsDelete.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.IsNew.ToString())))
				{
					businessObject.IsNew = dataReader.GetBoolean(dataReader.GetOrdinal(Sys_Users.Sys_UsersFields.IsNew.ToString()));
				}


        }

        /// <summary>
        /// Populate business objects from the data reader
        /// </summary>
        /// <param name="dataReader">data reader</param>
        /// <returns>list of Sys_Users</returns>
        internal List<Sys_Users> PopulateObjectsFromReader(IDataReader dataReader)
        {

            List<Sys_Users> list = new List<Sys_Users>();

            while (dataReader.Read())
            {
                Sys_Users businessObject = new Sys_Users();
                PopulateBusinessObjectFromReader(businessObject, dataReader);
                list.Add(businessObject);
            }
            return list;

        }

        #endregion

	}
}
